package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bean.Pagination;

import util.DBUtil;

import dao.BookDAO;
import entity.Book;
import entity.Product;

public class JdbcBookDAO implements BookDAO{
	@Override
	public List<Product> findHot(int size) throws Exception {
		List<Product> prods = new ArrayList<Product>();
		Connection conn = DBUtil.getConnction();
		//String sql = "select * from d_product where has_deleted=0 order by add_time desc limit 4";
		String sql = "select sum(i.product_num) as sum ,p.* from d_item i join d_product p on i.product_id=p.id where has_deleted=0 group by i.product_id order by sum desc limit 0,?";
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, size);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Product pro = new Product();
			pro.setId(rs.getInt("id"));
			pro.setProductName(rs.getString("product_name"));
			pro.setDescription(rs.getString("description"));
			pro.setAddTime(rs.getLong("add_time"));
			pro.setFixedPrice(rs.getDouble("fixed_price"));
			pro.setDangPrice(rs.getDouble("dang_price"));
			pro.setKeywords(rs.getString("keywords"));
			pro.setHasDeleted(rs.getInt("has_deleted"));
			pro.setProductPic(rs.getString("product_pic"));
			prods.add(pro);
		}
		return prods;
	}
	@Override
	public List<Product> findNew() throws Exception {
		List<Product> prods = new ArrayList<Product>();
		Connection conn = DBUtil.getConnction();
		Statement stat = conn.createStatement();
		String sql = "select * from d_product where has_deleted=0 order by add_time desc limit 0,4";
		ResultSet rs = stat.executeQuery(sql);
		while(rs.next()){
			Product pro = new Product();
			pro.setId(rs.getInt("id"));
			pro.setProductName(rs.getString("product_name"));
			pro.setDescription(rs.getString("description"));
			pro.setAddTime(rs.getLong("add_time"));
			pro.setFixedPrice(rs.getDouble("fixed_price"));
			pro.setDangPrice(rs.getDouble("dang_price"));
			pro.setKeywords(rs.getString("keywords"));
			pro.setHasDeleted(rs.getInt("has_deleted"));
			pro.setProductPic(rs.getString("product_pic"));
			prods.add(pro);
		}
		
		return prods;
	}
	@Override
	public List<Book> findRecommend() throws Exception {
		List<Book> books = new ArrayList<Book>();
		Connection conn = DBUtil.getConnction();
		Statement stat = conn.createStatement();
		String sql = "select a.author,a.publishing,a.publish_time,a.author_summary,b.fixed_price,b.dang_price,a.id,b.product_name,b.product_pic from d_book a join d_product b on a.id=b.id where b.has_deleted=0 limit 0,2";
		ResultSet rs = stat.executeQuery(sql);
		while(rs.next()){
			Book book = new Book();
			book.setAuthor(rs.getString(1));
			book.setPublishing(rs.getString(2));
			book.setPublishTime(rs.getLong(3));
			book.setAuthorSummary(rs.getString(4));
			book.setFixedPrice(rs.getDouble(5));
			book.setDangPrice(rs.getDouble(6));
			book.setId(rs.getInt(7));
			book.setProductName(rs.getString(8));
			book.setProductPic(rs.getString(9));
			books.add(book);
		}
		
		return books;
	}
	@Override
	public List<Book> findById(int id) throws Exception {
		List<Book> books = new ArrayList<Book>();
		Connection conn = DBUtil.getConnction();
		String sql="select b.id, b.author,b.publishing,b.publish_time,b.author_summary,c.product_pic,c.product_name,c.fixed_price,c.dang_price from d_category_product a join d_book b join d_product c on b.id=c.id and a.product_id=b.id and a.cat_id=?";
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, id);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Book book = new Book();
			book.setId(id);
			book.setAuthor(rs.getString(2));
			book.setPublishing(rs.getString(3));
			book.setPublishTime(rs.getLong(4));
			book.setAuthorSummary(rs.getString(5));
			book.setProductPic(rs.getString(6));
			book.setProductName(rs.getString(7));
			book.setFixedPrice(rs.getDouble(8));
			book.setDangPrice(rs.getDouble(9));
			books.add(book);
		}
		
		return books;
	}
	@Override
	public List<Book> findById(int id, Pagination p) throws Exception {
		List<Book> books = new ArrayList<Book>();
		Connection conn = DBUtil.getConnction();
		String sql="select b.id, b.author,b.publishing,b.publish_time,b.author_summary,c.product_pic,c.product_name,c.fixed_price,c.dang_price from d_category_product a join d_book b join d_product c on b.id=c.id and a.product_id=b.id and a.cat_id=? limit ?,?";
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, id);
		pst.setInt(2, p.getStartRow());
		pst.setInt(3, p.getPageSize());
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Book book = new Book();
			book.setId(rs.getInt("id"));
			book.setAuthor(rs.getString(2));
			book.setPublishing(rs.getString(3));
			book.setPublishTime(rs.getLong(4));
			book.setAuthorSummary(rs.getString(5));
			book.setProductPic(rs.getString(6));
			book.setProductName(rs.getString(7));
			book.setFixedPrice(rs.getDouble(8));
			book.setDangPrice(rs.getDouble(9));
			books.add(book);
		}
		return books;
	}
	@Override
	public Product findProById(int id) throws Exception {
		Product pro = null;
		Connection conn=DBUtil.getConnction();
		String sql = "select * from d_product where id=?";
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, id);
		ResultSet rs = pst.executeQuery();
		if(rs.next()){
			pro = new Product();
			pro.setId(rs.getInt("id"));
			pro.setFixedPrice(rs.getDouble("fixed_price"));
			pro.setDangPrice(rs.getDouble("dang_price"));
			pro.setProductName(rs.getString("product_name"));
			pro.setAddTime(rs.getLong("add_time"));
			pro.setProductPic(rs.getString("product_pic"));
		}
		return pro;
	}
	public static void main(String[] args) throws Exception{
		for (Product p : new JdbcBookDAO().findHot(4)) {
			System.out.println(p.getId());
		}
	}
	@Override
	public List<Book> findByProperty(String propertyName, String order, int id,Pagination p) throws Exception{
		List<Book> books = new ArrayList<Book>();
		Connection conn = DBUtil.getConnction();
		String sql="select b.id, b.author,b.publishing,b.publish_time,b.author_summary,c.product_pic,c.product_name,c.fixed_price,c.dang_price from d_category_product a join d_book b join d_product c on b.id=c.id and a.product_id=b.id and a.cat_id=? order by "+propertyName+" "+order+" limit ?,?";
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, id);
		pst.setInt(2, p.getStartRow());
		pst.setInt(3, p.getPageSize());
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Book book = new Book();
			book.setId(rs.getInt("id"));
			book.setAuthor(rs.getString(2));
			book.setPublishing(rs.getString(3));
			book.setPublishTime(rs.getLong(4));
			book.setAuthorSummary(rs.getString(5));
			book.setProductPic(rs.getString(6));
			book.setProductName(rs.getString(7));
			book.setFixedPrice(rs.getDouble(8));
			book.setDangPrice(rs.getDouble(9));
			books.add(book);
		}
		return books;
	}
	@Override
	public Book findBookById(int id) throws Exception {
		Connection conn = DBUtil.getConnction();
		String sql = "select b.*,p.* from d_book b join d_product p on b.id=p.id where b.id=?";
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, id);
		ResultSet rs = pst.executeQuery();
		Book pro = null;
		if(rs.next()){
			pro=new Book();
			pro.setId(rs.getInt("id"));
			pro.setProductName(rs.getString("product_name"));
			pro.setDescription(rs.getString("description"));
			pro.setAddTime(rs.getLong("add_time"));
			pro.setFixedPrice(rs.getDouble("fixed_price"));
			pro.setDangPrice(rs.getDouble("dang_price"));
			pro.setKeywords(rs.getString("keywords"));
			pro.setHasDeleted(rs.getInt("has_deleted"));
			pro.setProductPic(rs.getString("product_pic"));
			//设置book表字段
			pro.setAuthor(rs.getString("author"));
			pro.setPublishing(rs.getString("publishing"));
			pro.setPublishTime(rs.getLong("publish_time"));
			pro.setWordNumber(rs.getString("word_number"));
			pro.setWhichEdtion(rs.getString("which_edtion"));
			pro.setTotalPage(rs.getString("total_page"));
			pro.setPrintTime(rs.getLong("print_time"));
			pro.setIsbn(rs.getString("isbn"));
			pro.setAuthorSummary(rs.getString("author_summary"));
			pro.setCatalogue(rs.getString("catalogue"));
		}
		return pro;
	}
}
